﻿using System;
using System.Collections.Generic;
using System.Linq;

namespace MeresModeling
{
    public partial class MembershipGroup
    {
        public override string ToString()
        {
            return this.Name;
        }
    }

    public partial class RelationDiagram
    {
        public DateTime ModifiedDate { get; set; }
    }

    public partial class UiDat
    {
        public string CHash { get; set; }
    }


    public partial class MeresModel
    {
        const string SqlAllTable = "SELECT TABLE_NAME as Name from information_schema.tables";

        const string SqlAllColumns =
            "select column_name as Name from information_schema.columns where table_name = {0} order by ordinal_position";

        const string QueryPk = "SELECT u.COLUMN_NAME as ColumnName, c.CONSTRAINT_NAME as ConstrainName, c.TABLE_NAME as TableName FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS u ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME AND u.TABLE_NAME = c.TABLE_NAME WHERE  (c.CONSTRAINT_TYPE = 'PRIMARY KEY')";

        public List<DbTableModel> GetTableModels()
        {
            var tables = ExecuteStoreQuery<DbTableModel>(SqlAllTable).ToList();
            var keys = ExecuteStoreQuery<TableKey>(QueryPk).ToList();
            var dics = new Dictionary<string, HashSet<string>>();

            foreach (var tableKey in keys)
            {
                if (!dics.ContainsKey(tableKey.TableName))
                {
                    dics[tableKey.TableName] = new HashSet<string>();
                }
                dics[tableKey.TableName].Add(tableKey.ColumnName);
            }

            foreach (var t in tables)
            {
                var columns = ExecuteStoreQuery<DbTableColumnModel>(SqlAllColumns, t.Name).ToList();
                t.Columns = columns;
                columns.ForEach(c =>
                    {
                        if (dics[t.Name].Contains(c.Name))
                            c.IsPrimaryKey = true;
                        c.Table = t;
                    });
            }

            tables.ForEach(t => t.Columns = t.Columns.OrderBy(c => !c.IsPrimaryKey).ToList());


            return tables;
        }
    }

    public class TableKey
    {
        public string TableName { get; set; }
        public string ColumnName { get; set; }
        public string ConstrainName { get; set; }
        public override string ToString()
        {
            return string.Format("{0}-{1}:{2}", TableName, ColumnName, ConstrainName);
        }
    }
}
